from flask import Flask, render_template, request, redirect, url_for, jsonify
from flask_sqlalchemy import SQLAlchemy
import os

app = Flask(__name__)

# 数据库配置
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///tool_navigation.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

# 模型定义
class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False)
    icon = db.Column(db.String(80), nullable=False)
    color = db.Column(db.String(20), default='#165DFF')
    tools = db.relationship('Tool', backref='category', lazy=True)

class Tool(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False)
    url = db.Column(db.String(200), nullable=False)
    description = db.Column(db.String(200))
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=False)

# 创建数据库表
with app.app_context():
    db.create_all()

# 路由定义
@app.route('/')
def index():
    # 查询所有分类和工具
    categories = Category.query.all()
    tools = Tool.query.all()
    
    return render_template('index.html', categories=categories, tools=tools)

# API 路由 - 获取所有分类
@app.route('/api/categories', methods=['GET'])
def get_categories():
    categories = Category.query.all()
    result = [{'id': category.id, 'name': category.name, 'icon': category.icon, 'color': category.color} 
              for category in categories]
    return jsonify(result)

# API 路由 - 添加分类
@app.route('/api/categories', methods=['POST'])
def add_category():
    data = request.get_json()
    name = data.get('name')
    icon = data.get('icon')
    
    if not name or not icon:
        return jsonify({'error': 'Name and icon are required'}), 400
    
    # 生成随机颜色
    colors = ['#165DFF', '#36D399', '#8B5CF6', '#FBBD23', '#F87272', '#EC4899', '#10B981', '#3B82F6', '#6366F1', '#F97316']
    import random
    color = random.choice(colors)
    
    new_category = Category(name=name, icon=icon, color=color)
    db.session.add(new_category)
    db.session.commit()
    
    return jsonify({'id': new_category.id, 'name': new_category.name, 'icon': new_category.icon, 'color': new_category.color}), 201

# API 路由 - 更新分类
@app.route('/api/categories/<int:id>', methods=['PUT'])
def update_category(id):
    category = Category.query.get_or_404(id)
    data = request.get_json()
    
    category.name = data.get('name', category.name)
    category.icon = data.get('icon', category.icon)
    
    db.session.commit()
    
    return jsonify({'id': category.id, 'name': category.name, 'icon': category.icon, 'color': category.color})

# API 路由 - 删除分类
@app.route('/api/categories/<int:id>', methods=['DELETE'])
def delete_category(id):
    category = Category.query.get_or_404(id)
    
    # 检查是否有工具使用此分类
    if Tool.query.filter_by(category_id=id).count() > 0:
        return jsonify({'error': 'Cannot delete category with associated tools'}), 400
    
    db.session.delete(category)
    db.session.commit()
    
    return jsonify({'message': 'Category deleted successfully'})

# API 路由 - 获取所有工具
@app.route('/api/tools', methods=['GET'])
def get_tools():
    tools = Tool.query.all()
    result = [{'id': tool.id, 'name': tool.name, 'url': tool.url, 
               'description': tool.description, 'category_id': tool.category_id} 
              for tool in tools]
    return jsonify(result)

# API 路由 - 添加工具
@app.route('/api/tools', methods=['POST'])
def add_tool():
    data = request.get_json()
    name = data.get('name')
    url = data.get('url')
    description = data.get('description')
    category_id = data.get('category_id')
    
    if not name or not url or not category_id:
        return jsonify({'error': 'Name, url and category_id are required'}), 400
    
    new_tool = Tool(name=name, url=url, description=description, category_id=category_id)
    db.session.add(new_tool)
    db.session.commit()
    
    return jsonify({'id': new_tool.id, 'name': new_tool.name, 'url': new_tool.url, 
                   'description': new_tool.description, 'category_id': new_tool.category_id}), 201

# API 路由 - 更新工具
@app.route('/api/tools/<int:id>', methods=['PUT'])
def update_tool(id):
    tool = Tool.query.get_or_404(id)
    data = request.get_json()
    
    tool.name = data.get('name', tool.name)
    tool.url = data.get('url', tool.url)
    tool.description = data.get('description', tool.description)
    tool.category_id = data.get('category_id', tool.category_id)
    
    db.session.commit()
    
    return jsonify({'id': tool.id, 'name': tool.name, 'url': tool.url, 
                   'description': tool.description, 'category_id': tool.category_id})

# API 路由 - 删除工具
@app.route('/api/tools/<int:id>', methods=['DELETE'])
def delete_tool(id):
    tool = Tool.query.get_or_404(id)
    db.session.delete(tool)
    db.session.commit()
    
    return jsonify({'message': 'Tool deleted successfully'})

if __name__ == '__main__':
    app.run(debug=True)    